/*
 * Copyright (c) 2007 Erwin Verdonk.
 *
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 */

package com.erwinverdonk.interpreters
{
	import mx.collections.ArrayCollection;
	import mx.collections.XMLListCollection;
	import mx.collections.Sort;
	import mx.collections.SortField;
	import mx.utils.ObjectUtil;
	import flash.net.ObjectEncoding;
	import mx.utils.ObjectProxy;
	import mx.collections.ListCollectionView;
	import flash.xml.XMLNode;
	import flash.utils.ByteArray;
	import flash.utils.getQualifiedClassName;
	import mx.events.CollectionEvent;
	import flash.utils.setInterval;
	import flash.utils.setTimeout;
	import flash.utils.Timer;
	import mx.controls.Alert;

	public class SQLInterpreter
	{
		private var _databases:ArrayCollection;
		private var _primaryDatabase:XML;
		
		// Regular Expression Patterns
		private var queryPattern:RegExp = /^(\b(?:s|S)(?:e|E)(?:l|L)(?:e|E)(?:c|C)(?:t|T)\b *?(?:(?:d|D)(?:i|I)(?:s|S)(?:t|T)(?:i|I)(?:n|N)(?:c|C)(?:t|T) *?)?[^<& ]+?(?: *?, *?[^<& ]+?)*?) *?(\b(?:f|F)(?:r|R)(?:o|O)(?:m|M)\b *?[^<& ]+?)? *?(\b(?:w|W)(?:h|H)(?:e|E)(?:r|R)(?:e|E)\b(?: *?[^<&= ]+? *?(?:=|like) *?'[^&']+'(?: *?(?:a|A)(?:n|N)(?:d|D) *?[^<&= ]+? *?(?:=|like) *?'[^&']+')*?)+)? *?(\b(?:o|O)(?:r|R)(?:d|D)(?:e|E)(?:r|R) (?:b|B)(?:y|Y)\b *?(?:[^<& ](?: *?(?:(?:d|D)(?:e|E)(?:s|S)(?:c|C)|(?:a|A)(?:s|S)(?:c|C)))?)+?(?: *?, *?(?:[^<& ](?: *?(?:(?:d|D)(?:e|E)(?:s|S)(?:c|C)|(?:a|A)(?:s|S)(?:c|C)))?)+?)*?)? ?(\b(?:l|L)(?:i|I)(?:m|M)(?:i|I)(?:t|T)\b *?[0-9]+?(?: *?, *?[0-9]+?))?$/;
		private var querySelectPattern:RegExp = /^(\b(?:s|S)(?:e|E)(?:l|L)(?:e|E)(?:c|C)(?:t|T)\b)(?: +?(?:((?:d|D)(?:i|I)(?:s|S)(?:t|T)(?:i|I)(?:n|N)(?:c|C)(?:t|T)) *?)?([^<& ]+?(?: *?, *?[^<& ]+?)*?))?$/;
		private var queryFromPattern:RegExp = /^(\b(?:f|F)(?:r|R)(?:o|O)(?:m|M)\b)( *?)([^<& ]+?)$/;
		// private var queryJoinsPattern:RegExp = /(\b(?:inner|(?:left|right|full) outer) join\b)(?:(\s*[a-z0-9-_*=()., ]+?)(\bon\b)(\s*[a-z0-9-_*=()., ]+?))(?=inner join|(?:left|right) outer join|$)/;
		private var queryWherePattern:RegExp = /^(\b(?:w|W)(?:h|H)(?:e|E)(?:r|R)(?:e|E)\b)(?:(?: *?([^<&= ]+? *?(?:=|like) *?'[^&']+'(?: *?(?:a|A)(?:n|N)(?:d|D) *?[^<&= ]+? *?(?:=|like) *?'[^&']+')*?))+)?$/;
		private var queryOrderByPattern:RegExp = /^(\b(?:o|O)(?:r|R)(?:d|D)(?:e|E)(?:r|R) (?:b|B)(?:y|Y)\b)(?: *?((?:[^<& ](?: *?(?:(?:d|D)(?:e|E)(?:s|S)(?:c|C)|(?:a|A)(?:s|S)(?:c|C)))?)+?(?: *?, *?(?:[^<& ](?: *?(?:(?:d|D)(?:e|E)(?:s|S)(?:c|C)|(?:a|A)(?:s|S)(?:c|C)))?)+?)*?))?$/;
		private var queryLimitPattern:RegExp = /^(\b(?:l|L)(?:i|I)(?:m|M)(?:i|I)(?:t|T)\b)(?: *?([0-9]+?(?: *?, *?[0-9]+?)))?$/;
		
		// Constructor
		public function SQLInterpreter(databases:ArrayCollection)
		{
			this._databases = databases;
		}
		
		// Execute SQL Command
		public function execute(cmd:String):ArrayCollection
		{
			var commands:Array = cmd.split(";");
			var commandSplitted:Array;
			var command:String;
			var commandParameters:String;
			var results:Array = new Array();
			
			// Loop over commands
			for(var i:int=0;i<commands.length;i++)
			{
				commandSplitted = commands[i].match(/([a-z]+)(\s*.*)/i);
				command = commandSplitted[1];
				commandParameters = commandSplitted[2];
				
				switch(commandSplitted[1].toLowerCase())
				{
					case "use":
						this.useCommand(commandParameters.replace(/ /g,""));
						break;
					case "select":
						results.push(this.selectCommand(command+commandParameters));
						break;
				}
			}
			
			return results[0];
		}
		
		// USE
		private function useCommand(db:String):void
		{
			for(var i:int=0;i<this._databases.length;i++)
			{
				if(this._databases.getItemAt(i).name == db)
				{
					_primaryDatabase = new XML("<root>"+this._databases.getItemAt(i).db+"</root>") as XML;
					
					trace("Database with name '"+db+"', is set as primary!");
					break;
				}
			}
		}
		

		// SELECT
		private function selectCommand(q:String):ListCollectionView
		{
			var tabel:XMLListCollection;
			var returnTabel:ArrayCollection;
			var queryParts:Object = parseQuery(q);
			
			// STAP 1: Tabel scheiden van XML
			tabel = isolateTable(queryParts.from);
			
			// STAP 2: Filter toepassen op velden met where
			if(queryParts.where != null)
			{
				filterData(tabel,queryParts.where);
			}
			
			// STAP 3: Orderen op velden
			if(queryParts.orderBy != null)
			{
				orderFields(tabel,queryParts.orderBy);
			}
			
			// STAP 4: Limit
			 if(queryParts.limit != null)
			{ 
				tabel = limitResult(tabel,queryParts.limit);
			} 
			
			// STAP 5: Converteren naar ArrayCollection
			returnTabel = convertToArrayCollection(tabel,queryParts.select);
			
			// STAP 6: Verwijder dubbele items wanneer DISTINCT
			 if(queryParts.select != null && String(queryParts.select[2]).toLowerCase() == "distinct")
			{
				groupUniqueRows(returnTabel);
			} 
			
			return returnTabel 
		}
		
		private function parseQuery(q:String):Object
		{
			var queryMatches:Array;
			var querySelectMatches:Array;
			var queryFromMatches:Array;
			var queryWhereMatches:Array;
			var queryOrderByMatches:Array;
			var queryLimitMatches:Array;
			
			var queryParts:Object = new Object();
			
			// QUERY
			queryMatches = q.match(queryPattern);
			
			// SELECT and FROM (are required)
			querySelectMatches = String(queryMatches[1]).match(querySelectPattern);
			queryFromMatches = String(queryMatches[2]).match(queryFromPattern);
			
			queryParts.select = querySelectMatches;
			queryParts.from = queryFromMatches;
			
			// WHERE
			if(queryMatches.length >= 4 && queryMatches[3] != null)
			{
				queryWhereMatches = String(queryMatches[3]).match(queryWherePattern);
				queryParts.where = queryWhereMatches;
			}
			
			// ORDER BY
			if(queryMatches.length >= 5 && queryMatches[4] != null)
			{
				queryOrderByMatches = String(queryMatches[4]).match(queryOrderByPattern);
				queryParts.orderBy = queryOrderByMatches;
			}
			
			// LIMIT
			if(queryMatches.length >= 6 && queryMatches[5] != null)
			{
				queryLimitMatches = String(queryMatches[5]).match(queryLimitPattern);
				queryParts.limit = queryLimitMatches;
			}
			
			return queryParts;
		}
		
		private function isolateTable(queryFromMatches:Array):XMLListCollection
		{
			var realTabel:* = ObjectUtil.copy(this._primaryDatabase);
			var temp:Array = queryFromMatches[3].split(/\./);
			for(var e:int=0;e<temp.length;e++)
			{
				realTabel = realTabel[temp[e]];
			}
			var tabel:XMLListCollection = new XMLListCollection(new XMLList(realTabel));
			
			return tabel;
		}
		
		private function orderFields(tabel:XMLListCollection,queryOrderByMatches:Array):XMLListCollection
		{
			var tempSort:Sort = new Sort();
			var orders:Array;
			var order:Array
			var sortFields:Array = new Array();
			
			orders = queryOrderByMatches[2].split(/ *, */);
			for(var i:int=0;i<orders.length;i++)
			{
				order = orders[i].split(/ +/);
				if(order[1] != null && order[1].toLowerCase() == "desc")
				{
					order[1] = true;
				}
				else
				{
					order[1] = false;
				}
				sortFields.push(new SortField(order[0],false,order[1]));
			}
			tempSort.fields = sortFields;
			tabel.sort = tempSort;
			tabel.refresh();
			
			return tabel;
		}
		
		private function filterData(tabel:XMLListCollection,queryWhereMatches:Array):void
		{
			var wheres:Array = queryWhereMatches[2].split(/(?<=') *\band\b */i);
			var wheresMatched:Array = new Array();
			var where:Array;
			var whereLike:Array;
			var returnValue:Boolean = false;
			var i:int;
			var j:int;
			var item:Object;
	
			// Loop over wheres
			for(i=0;i<wheres.length;i++)
			{
				where = wheres[i].split(/ *= */);
				whereLike = wheres[i].split(/ *like */i);
				
				// WHERE =
				if(where.length > 1)
				{
					// Loop over columns in table row
					for(j=0;j<tabel.length;j++)
					{
						item = tabel.getItemAt(j);
						
						if(String(item[where[0]]).replace(/^[ \t]+|[ \t]+$/,"") != String(String(where[1]).replace(/[']/g,"")))
						{
							tabel.removeItemAt(j);
							j--
						}
					}
				}
				
				// WHERE LIKE
				else if(whereLike.length > 1)
				{
					// Loop over columns in table row
					for(j=0;j<tabel.length;j++)
					{
						item = tabel.getItemAt(j);

						if(
						
						(String(whereLike[1]).replace(/[']/g,"").match(new RegExp("(?<!%)"+String(whereLike[1]).replace(/['%]/g,"")+"(?=%)")) && !String(item[whereLike[0]]).match(new RegExp("^"+String(whereLike[1]).replace(/['%]/g,""))))
						||
						(String(whereLike[1]).replace(/[']/g,"").match(new RegExp("(?<=%)"+String(whereLike[1]).replace(/['%]/g,"")+"(?!%)")) && !String(item[whereLike[0]]).match(new RegExp(String(whereLike[1]).replace(/['%]/g,"")+"$")))
						||
						(String(whereLike[1]).replace(/[']/g,"").match(new RegExp("(?<=%)"+String(whereLike[1]).replace(/['%]/g,"")+"(?=%)")) && !String(item[whereLike[0]]).match(new RegExp(String(whereLike[1]).replace(/['%]/g,""))))
						)
						{
							tabel.removeItemAt(j);
							j--
						}
					}
				}
			}
		}
		
		private function limitResult(tabel:XMLListCollection,queryLimitMatches:Array):XMLListCollection
		{
			var newTabel:XMLListCollection = new XMLListCollection();

			var limit:Array = queryLimitMatches[2].split(/ *, */);
			
			for(var i:int=0;i<tabel.length;i++)
			{
				if(i+1 >= Number(limit[0]) && i+1 <=Number(limit[1]))
				{
					newTabel.addItem(tabel.getItemAt(i));
				}
			}
			
			newTabel.sort = tabel.sort;
			newTabel.refresh();
			tabel = newTabel; 
			
			return tabel;
		}
		
		private function convertToArrayCollection(tabel:XMLListCollection,querySelectMatches:Array):ArrayCollection
		{
			var returnTabel:ArrayCollection = new ArrayCollection
			var children:XMLList;
			var name:String;
			
			// Loop over rijen
			for(var i:int=0;i<tabel.length;i++)
			{
				children = new XMLList(tabel.getItemAt(i)).children();
				
				var tabelColumns:Object =  new Object();
				
				// Loop over kolommen
				for(var key2:String in children)
				{
					name = children[key2].name();
					
					// Als kolom voorkomt in de select statement mag de kolom opgenomen worden in de ArrayCollection.
					if(new RegExp("("+name+")|\\*",i).test(String(querySelectMatches)))
					{
						// If numeric store it as Number, else as String.
						if(Number(children[key2]).toString() != "NaN" && new RegExp("\w").test(String(children[key2])))
						{
							tabelColumns[name] = Number(children[key2]);
						}
						else
						{
							tabelColumns[name] = children[key2];
						}
					}
				}
				returnTabel.addItemAt(tabelColumns,i);
			}
			
			return returnTabel;
		}
		
		private function groupUniqueRows(returnTabel:ArrayCollection):void
		{
			for(var i:int=0;i<returnTabel.length;i++)
			{
				var item:String = ObjectUtil.toString(returnTabel.getItemAt(i));
				
				for(var j:int=0;j<returnTabel.length;j++)
				{
					var itemCompare:String = ObjectUtil.toString(returnTabel.getItemAt(j));
					var result:int = ObjectUtil.compare(item,itemCompare);
					
					if(result == 0 && i != j)
					{
						returnTabel.removeItemAt(j);
						j--
					}
				}
			}
		}
	}
}
